package com.hb.trade.api.expert.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.json.JSONUtil;
import com.hb.core.pojo.Attachment;
import com.hb.core.pojo.Attachments;
import com.hb.core.pojo.expert.dto.ExpertGroupDTO;
import com.hb.core.pojo.expert.dto.ExpertGroupMembersDTO;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.fzzn.core.file.entity.HbFileInfo;
import org.fzzn.core.file.entity.UploadEntity;
import org.fzzn.core.file.service.PersistenceService;
import org.fzzn.core.model.constant.CommonConstant;
import org.fzzn.core.util.IdUtil;
import org.fzzn.core.web.util.SpringUtils;

import java.io.*;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * 导出抽取专家信息
 * @author tian
 * @date 2022年06月20日 11:10
 */
public class ExportExtractExpertUtil {


    /**
     * 生成评委会信息
     *
     * @author tian
     * @date 2022-6-18 15:57
     */
    public static Attachment expertExcel(ExpertGroupDTO expertMajorDTO) throws IOException {
        Attachment attachment = new Attachment();
        InputStream in = null;
        ByteArrayOutputStream bos = null;
        try {
            PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
            // 专家信息
            List<ExpertGroupMembersDTO> expertGroupMembersList = expertMajorDTO.getExpertList();

            InputStream stream = SpringUtils.getLocalStream("template/expertGroupMembers.xlsx");
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(0);
            //生成表头
            XSSFRow row = sheetAt.getRow(1);
            row.getCell(1).setCellValue(expertMajorDTO.getProjectName());
            row.getCell(6).setCellValue(expertMajorDTO.getProjectCode());

            row = sheetAt.getRow(2);
            row.getCell(1).setCellValue(expertMajorDTO.getBiddingOrganizeName());
            row.getCell(6).setCellValue(expertMajorDTO.getAgencyOrganizeName());

            row = sheetAt.getRow(3);
            row.getCell(1).setCellValue(expertMajorDTO.getBidWayName());
            // 开标时间
            if (null != expertMajorDTO.getOpenBidTime()) {
                row.getCell(6).setCellValue(expertMajorDTO.getOpenBidTime().format(formatter));
            }

            row = sheetAt.getRow(4);
            row.getCell(1).setCellValue(expertGroupMembersList.size() + "人");
            // 抽取时间
            row.getCell(6).setCellValue(expertMajorDTO.getCreateTime().format(formatter));


            // 专家信息从第7行开始创建
            int rowNum = 7;
            // 组建专家信息
            for (int i = 0; i < expertGroupMembersList.size(); i++) {
                ExpertGroupMembersDTO ExpertGroupMembersDTO = expertGroupMembersList.get(i);
                sheetAt.copyRows(6, 6, rowNum + i, new CellCopyPolicy());

                row = sheetAt.getRow(rowNum + i);
                row.getCell(0).setCellValue(ExpertGroupMembersDTO.getExpertName());
                if (null == ExpertGroupMembersDTO.getSex()) {
                    row.getCell(1).setCellValue("/");
                } else {
                    row.getCell(1).setCellValue(ExpertGroupMembersDTO.getSex().equals(0) ? "男" : "女");
                }
                row.getCell(2).setCellValue(ExpertGroupMembersDTO.getPhoneNum());
                row.getCell(3).setCellValue(ExpertGroupMembersDTO.getIdCard());
                row.getCell(6).setCellValue(ExpertGroupMembersDTO.getMajorName());
                row.getCell(7).setCellValue(ExpertGroupMembersDTO.getCompany());
            }

            // 末尾行
            int lastRowNum = rowNum + expertGroupMembersList.size();
            row = sheetAt.createRow(lastRowNum);
            row.setHeight((short) (30 * 20));

            XSSFCellStyle cellStyle = row.createCell(0).getCellStyle();
            //设置水平对齐的样式为居中对齐;
            //cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            XSSFCell cell1 = row.createCell(0);
            cell1.setCellValue("抽取人：" + expertMajorDTO.getOrgName());
            cell1.setCellStyle(cellStyle);

            XSSFCell cell2 = row.createCell(3);
            cell2.setCellValue("监督人员：");
            cell2.setCellStyle(cellStyle);

            // 合并单元格
            createCellRangeAddress(sheetAt, lastRowNum, lastRowNum, 0, 2);
            createCellRangeAddress(sheetAt, lastRowNum, lastRowNum, 3, 8);

            bos = new ByteArrayOutputStream();
            templateWorkbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);

            // 上传
            File scheduleExcel = FileUtil.writeBytes(brray, new File(CommonConstant.TEMP_PATH + "/" + expertMajorDTO.getProjectCode() + "/" + "专家抽取表" + ".xlsx"));
            UploadEntity uploadEntity = new UploadEntity(scheduleExcel);
            List<HbFileInfo> hbFileInfos = persistenceService.uploadServer(uploadEntity);
            if (CollUtil.isNotEmpty(hbFileInfos)) {
                // 保存入库
                Attachments attachments = new Attachments();
                attachment = JSONUtil.toBean(JSONUtil.parseObj(hbFileInfos.get(0)), Attachment.class);
                attachment.setId(IdUtil.getId());
                attachments.add(attachment);

                return attachment;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != in) {
                in.close();
            }
            if (null != bos) {
                bos.close();
            }
        }
        return attachment;
    }


    public static void createCellRangeAddress(XSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol){
        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        // 这里是合并excel中日期的两行为一行
        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellAddresses);
    }
}
